using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VGPBST
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                case "YGR":
                case "TST":
                case "SXN":
                case "UBT":
                default:
                    #region=========
                    dwReturn["GPBMANDANT"] = "00";
                    dwReturn["GPBPARTIE"] = "0002956";
                    dwReturn["GPBPARTIEKZ"] = "0";
                    dwReturn["GPBLO"] = "01";
                    dwReturn["GPBLB"] = "CP";
                    dwReturn["GPBAENDZL"] = "0";
                    dwReturn["GPBAENDBEN"] = "        ";
                    dwReturn["GPBAENDDAT"] = "0";
                    dwReturn["GPBNEUBEN"] = "JAKV    ";
                    dwReturn["GPBNEUDAT"] = "20150108";
                    dwReturn["GPBART"] = "45021.NE.20.1       ";
                    dwReturn["GPBVAR"] = "   ";
                    dwReturn["GPBFARBE"] = "Y8581S    ";
                    dwReturn["GPBDEZI"] = " ";
                    dwReturn["GPBBSTKGG"] = "1120000";
                    dwReturn["GPBBSTKAG"] = "56";
                    dwReturn["GPBBSTSPG"] = "0";
                    dwReturn["GPBBSTKGZ"] = "0";
                    dwReturn["GPBBSTKAZ"] = "0";
                    dwReturn["GPBBSTSPZ"] = "0";
                    dwReturn["GPBEINDAT"] = "20141229";
                    dwReturn["GPBSPPART"] = "          ";
                    dwReturn["GPBZWPART"] = "          ";
                    dwReturn["GPBFILLER"] = "                            ";
                    #endregion
                    break;
            }
           
        }
        public DataRow Convert2GPBST(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryGPBST(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                default:
                    dwReturn["GPBMANDANT"] = "00";
                    dwReturn["GPBPARTIE"] = "0002956";
                    dwReturn["GPBPARTIEKZ"] = "0";
                    dwReturn["GPBLO"] = "01";
                    dwReturn["GPBLB"] = "CP";
                    dwReturn["GPBAENDZL"] = "0";
                    dwReturn["GPBAENDBEN"] = "        ";
                    dwReturn["GPBAENDDAT"] = "0";
                    dwReturn["GPBNEUBEN"] = "JAKV    ";
                    dwReturn["GPBNEUDAT"] = "20150108";
                    dwReturn["GPBART"] = "45021.NE.20.1       ";
                    dwReturn["GPBVAR"] = "   ";
                    dwReturn["GPBFARBE"] = "Y8581S    ";
                    dwReturn["GPBDEZI"] = " ";
                    dwReturn["GPBBSTKGG"] = "1120000";
                    dwReturn["GPBBSTKAG"] = "56";
                    dwReturn["GPBBSTSPG"] = "0";
                    dwReturn["GPBBSTKGZ"] = "0";
                    dwReturn["GPBBSTKAZ"] = "0";
                    dwReturn["GPBBSTSPZ"] = "0";
                    dwReturn["GPBEINDAT"] = "20141229";
                    dwReturn["GPBSPPART"] = "          ";
                    dwReturn["GPBZWPART"] = "          ";
                    dwReturn["GPBFILLER"] = "                            ";

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }

        public void SaveGPBST(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryGPBST(dwData, 1);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapGPBST(ref dwLocal, dwData);
            switch (ITXDBVERSION)
            {
                case "YGR":
                default:
                    if (!flgExist)
                    {
                        InsertGPBST(dwLocal, 1);
                        UpdateGPBST(dwLocal, 1);
                    }
                    else
                    {
                        UpdateGPBST(dwLocal, 3);
                    }
                    break;
            }
        }
        public void MapGPBST(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public DataSet QueryGPBST(DataRow dwData, int option = 1)
        { 
            switch (option)
            {
                case 0:
                    SResult = QGPB00(dwData);
                    break;
                case 1:
                    SResult = QGPB01(dwData);
                    break;
                case 2:
                    SResult = QGPB02(dwData);
                    break; 
            } 
            return SResult;
        }
        public DataSet QGPB00(DataRow dwData)
        {


            or1 = OracleDB.CreateCommand();

            or1.CommandText = "SELECT * FROM GPBST WHERE  ROWNUM<2 ";

            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGPB01(DataRow dwData)
        { 
            or1 = OracleDB.CreateCommand();

            or1.CommandText = "SELECT * FROM GPBST WHERE GPBLO=:GPBLO: AND GPBLB=:GPBLB: AND GPBMANDANT=:GPBMANDANT: AND GPBPARTIE=:GPBPARTIE: AND GPBPARTIEKZ=:GPBPARTIEKZ: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QGPB02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();

            or1.CommandText = "SELECT * FROM GPBST WHERE GPBLO=:ZULO: AND GPBLB=:ZULB: AND GPBMANDANT=:ZUMANDANT: AND GPBPARTIE=:ZUPARTIE: AND GPBPARTIEKZ=:ZUPARTIEKZ: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult; 
        }

        public void InsertGPBST(DataRow dwData, int option = 1)
        {


            or1 = OracleDB.CreateCommand();
            switch (app.company)
            {
                case "YGR":
                case "TST":
                case "SXN":
                case "UBT":
                default:
                    or1.CommandText = "INSERT INTO GPBST ( GPBMANDANT,GPBPARTIE,GPBPARTIEKZ,GPBLO,GPBLB,GPBAENDZL,GPBAENDBEN,GPBAENDDAT,GPBNEUBEN,GPBNEUDAT,GPBART,GPBVAR,GPBFARBE,GPBDEZI,GPBBSTKGG,GPBBSTKAG,GPBBSTSPG,GPBBSTKGZ,GPBBSTKAZ,GPBBSTSPZ,GPBEINDAT,GPBSPPART,GPBZWPART,GPBFILLER)"
   + " VALUES (:GPBMANDANT:,:GPBPARTIE:,:GPBPARTIEKZ:,:GPBLO:,:GPBLB:,:GPBAENDZL:,:GPBAENDBEN:,:GPBAENDDAT:,:GPBNEUBEN:,:GPBNEUDAT:,:GPBART:,:GPBVAR:,:GPBFARBE:,:GPBDEZI:,:GPBBSTKGG:,:GPBBSTKAG:,:GPBBSTSPG:,:GPBBSTKGZ:,:GPBBSTKAZ:,:GPBBSTSPZ:,:GPBEINDAT:,:GPBSPPART:,:GPBZWPART:,:GPBFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

                    break; 
            }

            Connect2Proxy(or1);
        }
        public void UpdateGPBST(DataRow dwData, int option = 0)
        {

            or1 = OracleDB.CreateCommand();
            if (option == 0)
            {
                switch (app.company)
                {
                    case "YGR":
                    case "TST":
                    case "SXN":
                    case "UBT":
                    default:
                        UGB01(dwData);
                        break;
                }
            }
            switch (option)
            {
                case 2:
                    UGB02(dwData);
                    break;
                case 3:
                    UGB03(dwData);
                    break;
                case 4:
                    UGB04(dwData);
                    break;
                case 5:
                    UGB05(dwData);
                    break;
            }


        }
        public void UGB01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE GPBST SET GPBMANDANT=:GPBMANDANT:,GPBPARTIE=:GPBPARTIE:,GPBPARTIEKZ=:GPBPARTIEKZ:,GPBLO=:GPBLO:,GPBLB=:GPBLB:,GPBAENDZL=:GPBAENDZL:,GPBAENDBEN=:GPBAENDBEN:,GPBAENDDAT=:GPBAENDDAT:,GPBART=:GPBART:,GPBVAR=:GPBVAR:,GPBFARBE=:GPBFARBE:,GPBDEZI=:GPBDEZI:,GPBBSTKGG=:GPBBSTKGG:,GPBBSTKAG=:GPBBSTKAG:,GPBBSTSPG=:GPBBSTSPG:,GPBBSTKGZ=:GPBBSTKGZ:,GPBBSTKAZ=:GPBBSTKAZ:,GPBBSTSPZ=:GPBBSTSPZ:,GPBEINDAT=:GPBEINDAT:,GPBSPPART=:GPBSPPART:,GPBZWPART=:GPBZWPART:,GPBFILLER=:GPBFILLER:"
                + " WHERE  GPBLO=:GPBLO: AND GPBLB=:GPBLB: AND  GPBPARTIE=:GPBPARTIE: AND GPBPARTIEKZ=:GLPARTIEKZ:"; 
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void UGB02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();

            or1.CommandText = "UPDATE GPBST SET  GPBBSTKGZ=:GPBBSTKGZ: "
                       + " WHERE  GPBLO=:GPBLO: AND GPBLB=:GPBLB: AND  GPBPARTIE=:GPBPARTIE: AND GPBPARTIEKZ=:GLPARTIEKZ:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void UGB03(DataRow dwData)
        {
            or1.CommandText = "UPDATE GPBST SET (GPBBSTKGG,GPBBSTKAG,GPBBSTSPG)=(SELECT nvl(SUM(GLBSTKG),0),nvl(SUM(GLBSTKA),0),NVL(SUM(GLBSTSP),0) FROM GLLG"
                        + " WHERE  GPBMANDANT=GLMANDANT AND GPBPARTIE=GLPARTIE AND GPBPARTIEKZ=GLPARTIEKZ AND GPBLO=GLLO AND GPBLB=GLLB)"
                        + " WHERE GPBMANDANT='00' AND GPBPARTIE=:GPBPARTIE: AND GPBPARTIEKZ=:GPBPARTIEKZ: AND GPBLO=:GPBLO: AND GPBLB=:GPBLB: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
            UGB04(dwData);

        }
        public void UGB04(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();

            or1.CommandText = "UPDATE GPBST SET (GPBBSTKGZ,GPBBSTKAZ,GPBBSTSPZ)=(SELECT NVL(SUM(ZUEINTKG),0),NVL(SUM(ZUEINTKA),0),NVL(SUM(ZUEINTSP),0) FROM ZUTEI"
                       + " WHERE  GPBMANDANT=ZUMANDANT AND GPBPARTIE=ZUPARTIE AND GPBPARTIEKZ=ZUPARTIEKZ AND GPBLO=ZULO AND GPBLB=ZULB)"
                       + " WHERE GPBMANDANT='00' AND GPBPARTIE=:GPBPARTIE: AND GPBPARTIEKZ=:GPBPARTIEKZ: AND GPBLO=:GPBLO: AND GPBLB=:GPBLB: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }
        public void UGB05(DataRow dwData)
        {

            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE GPBST SET (GPBBSTKGZ,GPBBSTKAZ,GPBBSTSPZ)=(SELECT NVL(SUM(ZUEINTKG),0),NVL(SUM(ZUEINTKA),0),NVL(SUM(ZUEINTSP),0) FROM ZUTEI"
               + " WHERE  GPBMANDANT=ZUMANDANT AND GPBPARTIE=ZUPARTIE AND GPBPARTIEKZ=ZUPARTIEKZ AND GPBLO=ZULO AND GPBLB=ZULB)"
               + " WHERE GPBMANDANT='00' AND GPBPARTIE=:GPBPARTIE: AND GPBPARTIEKZ=:GPBPARTIEKZ: AND GPBLO=:GPBLO: AND GPBLB=:GPBLB: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);

        }
        public void DeleteGPBST(DataRow dwData, int option = 1)
        {
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 2:
                    or1.CommandText = " DELETE  GPBST"
                         + " WHERE GPBMANDANT='00' AND GPBPARTIE=:GPBPARTIE AND GPBPARTIEKZ=:GPBPARTIEKZ AND GPBLO=:GPBLO AND GPBLB=:GPBLB "
                        + "  AND NOT EXISTS (SELECT 1 FROM GLLG WHERE  GPBMANDANT=GLMANDANT AND GPBPARTIE=GLPARTIE AND GPBPARTIEKZ=GLPARTIEKZ AND GPBLO=GLLO AND GPBLB=GLLB)";
                    or1.Parameters.Add("GPBLO", OracleType.NVarChar).Value = dwData["GPBLO"].ToString();
                    or1.Parameters.Add("GPBLB", OracleType.NVarChar).Value = dwData["GPBLB"].ToString();
                    or1.Parameters.Add("GPBPARTIE", OracleType.NVarChar).Value = dwData["GPBPARTIE"].ToString();
                    or1.Parameters.Add("GPBPARTIEKZ", OracleType.NVarChar).Value = dwData["GPBPARTIEKZ"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
    }
}
